package co.com.inventarios.persistencia;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.logging.Level;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import org.hibernate.Session;
import co.com.inventarios.modelo.Probodega;
import co.com.inventarios.utils.HibernateSessionFactory;

public class QueryProBodegaDAO {
	
	
	private EntityManager getEntityManager() {
		return EntityManagerHelper.getEntityManager();
	}
	
	
	public Double procedimiento(String producto, String serie, String empresa, String ccosto, String venbodega) {
		
		//System.out.print("=== Producto"+producto+"== Serie"+serie+"==BOdega"+venbodega+"====");
		
		Session session = HibernateSessionFactory.getSessionFactory()
				.getCurrentSession();
		session.getTransaction().begin();
		Connection con = session.connection();
		Double retorno = Double.valueOf("0");
		try {
			CallableStatement call = con
			//producto_existencia(pcproducto varchar2, pcserie varchar2, pcempresa varchar2, pcccosto varchar2 , pcvendedor varchar2, pnnumerror out number, pcdeserror out varchar2, pncantidad out number, pcunimed out varchar2);
					.prepareCall("{call PKG_MNG_INVENTARIO.producto_existencia(?,?,?,?,?,?,?,?,?)}");
			call.setString(1, producto);
			call.setString(2, serie);
			call.setString(3, empresa);
			call.setString(4, ccosto);
			call.setString(5, venbodega);
			call.registerOutParameter(6, Types.INTEGER);
			call.registerOutParameter(7, Types.CHAR);
			call.registerOutParameter(8, Types.DOUBLE);
			call.registerOutParameter(9, Types.CHAR);
			call.execute();
			// System.out.println("salida procedimiento -> "+call.getInt(3));
			retorno = call.getDouble(8);
			
			if (call.getInt(6) != 0 && retorno == null){
				retorno = Double.valueOf("0");
			}
	
			call.close();
			con.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	
		session.getTransaction().commit();
	
		return retorno;
	}

	
	public List<Probodega> findByPropertys(String bodega, String propiedad ,String espresion) throws Exception {

		EntityManagerHelper.log("finding Probodega instance with property: "
				+ propiedad + ", value: " + bodega, Level.INFO, null);
		try {
			//getEntityManager().clear();
			final String queryString = "select model from Probodega model where model.bodega.bodcodigo = '"
					+ bodega + "'  " + propiedad+ "  "+espresion+"  ";
			Query query = getEntityManager().createQuery(queryString);

			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	
	
	}	
	
	public List<Probodega> findByPropertys_all(String propiedad, String valor ,String espresion) throws Exception {

		EntityManagerHelper.log("finding Probodega instance with property: "
				+ propiedad + ", value: " + propiedad, Level.INFO, null);
		try {
			//getEntityManager().clear();
			final String queryString = "select model from Probodega model where model."+propiedad+" = '"
					+ valor + "'  " + "  "+espresion+"  ";
			Query query = getEntityManager().createQuery(queryString);

			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}			
	
}
